package com.example.demo.util;


import com.example.demo.bean.MonitorFoldLineTable;
import java.io.File;
import java.nio.file.Files;
import java.util.ArrayList;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
/**
 * @program: SpringAOP
 * @ClassName FileExportUtil
 * @description:
 * @author: zsn
 * @create: 2025-02-11 09:54
 * @Version 1.0
 **/
public class FileExportUtil {

    public static void testExcelDemo(List<MonitorFoldLineTable> list, HttpServletResponse response) throws IOException {
        /** 第一步，创建一个Workbook，对应一个Excel文件  */
        XSSFWorkbook wb = new XSSFWorkbook();
        /** 第二步，在Workbook中添加一个sheet,对应Excel文件中的sheet  */
        XSSFSheet sheet = wb.createSheet("excel导出标题");
        /** 第三步，设置样式以及字体样式*/
        XSSFCellStyle titleStyle = createTitleCellStyle(wb);
        XSSFCellStyle headerStyle = createHeadCellStyle(wb);
        XSSFCellStyle contentStyle = createContentCellStyle(wb);
        /** 第四步，创建标题 ,合并标题单元格 */
        // 行号
        int rowNum = 0;
        // 创建第一页的第一行，索引从0开始
        XSSFRow row0 = sheet.createRow(rowNum++);
        row0.setHeight((short) 600);// 设置行高

        String[] row_Text = {"测点编号", "单次变化量", "", "", "累计变化量", "", "", "变形速率", "", "", "监测时间"};
        for (int i = 0; i < row_Text.length; i++) {
            XSSFCell c00 = row0.createCell(i);
            c00.setCellValue(row_Text[i]);
            c00.setCellStyle(headerStyle);
        }
        // 合并单元格，参数依次为起始列，结束列，起始行，结束行 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));//标题合并单元格操作，4为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 3));//标题合并单元格操作，4为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 6));//标题合并单元格操作，4为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9));//标题合并单元格操作，4为总列数
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 10, 10));//标题合并单元格操作，4为总列数
        //第二行
        XSSFRow row2 = sheet.createRow(rowNum++);
        row2.setHeight((short) 700);
        String[] row_third = {"", "施工监测", "第三方监测", "前后差值", "施工监测", "第三方监测", "前后差值", "施工监测", "第三方监测", "前后差值"};
        for (int i = 0; i < row_third.length; i++) {
            XSSFCell tempCell = row2.createCell(i);
            tempCell.setCellValue(row_third[i]);
            tempCell.setCellStyle(headerStyle);
        }


        for (MonitorFoldLineTable value : list) {

            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < 12; j++) {
                //列宽自适应，j为自适应的列，true就是自适应，false就是不自适应，默认不自适应
                sheet.autoSizeColumn(j, true);
                XSSFCell tempCell = tempRow.createCell(j);
                tempCell.setCellStyle(contentStyle);
                String tempValue = "";
                switch (j) {
                    case 0:
                        tempValue = value.getCdbh();
                        break;
                    case 1:
                        tempValue = value.getSd();
                        break;
                    case 2:
                        tempValue = value.getDcsg().toString();
                        break;
                    case 3:
                        tempValue = value.getDcdsf().toString();
                        break;
                    case 4:
                        tempValue = value.getDcc().toString();
                        break;
                    case 5:
                        tempValue = value.getLjsg().toString();
                        break;
                    case 6:
                        tempValue = value.getLjdsf().toString();
                        break;
                    case 7:
                        tempValue = value.getLjc().toString();
                        break;
                    case 8:
                        tempValue = value.getBxsg().toString();
                        break;
                    case 9:
                        tempValue = value.getBxdsf().toString();
                        break;
                    case 10:
                        tempValue = value.getBxc().toString();
                        break;
                    case 11:
                        tempValue = value.getTime();
                        break;
                }
                tempCell.setCellValue(tempValue);
            }
        }
        //导出到浏览器下载
//        buildExcelDocument("监测数据", wb, response);
        wb.write(Files.newOutputStream(new File("D:\\" + "测试mulHeader1.xls").toPath()));
    }

    /**
     * @Description: [导出到浏览器]
     * @Param: [fileName, wb, response]
     * @return: void
     * @Author: yangyongzhuo
     * @Date: 2021/7/25 9:40
     */
    private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {
        try {
            response.setContentType("application/octet-stream");
            // 可自行定义编码格式
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            //清除jsp编译html文件的空白,防止excel出现空行
            response.flushBuffer();
            //写出
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(wb);
        }
    }

    public static void main(String[] args) throws IOException {
        //假设这是需要导出的数据
        List<MonitorFoldLineTable> data = new ArrayList();
        //调用导出工具类
        FileExportUtil.testExcelDemo(data,null);
    }
//---------------------------------------以下是封装的样式-----------------------------

    /**
     * 创建标题样式
     *
     * @param wb
     * @return
     */
    private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色

        XSSFFont headerFont1 = (XSSFFont) wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
        return cellStyle;
    }

    /**
     * 创建表头样式
     *
     * @param wb
     * @return
     */
    private static XSSFCellStyle createHeadCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }

    /**
     * 创建内容样式
     *
     * @param wb
     * @return
     */
    private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成12号字体
        XSSFFont font = wb.createFont();
        font.setColor((short) 8);
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);

        return cellStyle;
    }
}